﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Settings;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using Utils;

namespace PanasonicWarranty.Business
{
    public partial class pa_Model : BaseClass
    {
        public static List<pa_Model> Paging(int currentPage, int pageSize, ref int totalRecord,
                                            string modelCode, string cateCode, int? period,
                                            string periodtype, bool? happycall)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_Models.OrderBy(p => p.ModelCode).AsQueryable();
                if (!string.IsNullOrWhiteSpace(modelCode))
                {
                    resutl = resutl.Where(p => p.ModelCode.Contains(modelCode));
                }
                if (!string.IsNullOrWhiteSpace(cateCode))
                {
                    resutl = resutl.Where(p => p.CateCode.Contains(cateCode));
                }
                if (period != null)
                {
                    resutl = resutl.Where(p => p.WarrantyPeriod == period && p.PeriodType == periodtype);
                }
                if (happycall != null)
                {
                    resutl = resutl.Where(p => p.ApprovedRequired == happycall);
                }
                totalRecord = resutl.Count();
                return resutl.Skip(((currentPage - 1) < 0 ? 0 : (currentPage - 1)) * pageSize).Take(pageSize).ToList();
            }
        }

        public static DataTable GetAll()
        {
            return ExecuteReader("usp_pa_Model_GetAll", CommandType.StoredProcedure, null);
        }

        public static DataTable GetAll(bool? disable)
        {
            return ExecuteReader("usp_pa_Model_GetAll", CommandType.StoredProcedure, new SqlParameter("@Disable", disable ?? (object)DBNull.Value));
        }

        public static DataTable GetByCategory(string category, bool? disable)
        {
            string query = "SELECT * FROM pa_Model pm WHERE 1=1 ";
            if (!string.IsNullOrWhiteSpace(category))
                query += "AND pm.CateCode = '" + category + "' ";
            if (disable.HasValue)
                query += string.Format("AND [Disabled] = {0}", disable.Value ? "1" : "0 OR [Disabled] IS NULL");
            return ExecuteReader(query, CommandType.Text);
        }

        public static string GetCaption(string columnName)
        {
            switch (columnName)
            {
                case "ModelCode":
                    return "Model";
                case "CateCode":
                    return "Loại sản phẩm";
                case "WarrantyPeriod":
                    return "Thời hạn BH (tháng)";
                case "PeriodType":
                    return "Đơn vị thời gian";
                case "Description":
                    return "Chi tiết";
                case "ApprovedRequired":
                    return "Yêu cầu Happy call";
                case "ApprovedDuration":
                    return "Thời hạn xác nhận BH (ngày)";
            }
            return "";
        }

        public static int BulkInsert(string filePath)
        {
            int affected = 0;
            string tempTableName = "Temp" + Guid.NewGuid().ToString("N");
            try
            {
                //Tạo bảng tạm chứa dữ liệu
                ExecuteQuery(string.Format("SELECT TOP(0) * INTO {0} FROM pa_Model", tempTableName));

                //Insert dữ liệu vào bảng tạm
                using (SqlBulkCopy bc = new SqlBulkCopy(AppSetting.ConnectionString))
                {
                    bc.BulkCopyTimeout = 0;
                    bc.DestinationTableName = tempTableName;
                    using (OleDbConnection Oleconn = GetOleDbConnection(filePath))
                    {
                        Oleconn.Open();
                        var dtSchema = Oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        var sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                        using (OleDbCommand Olecmd = Oleconn.CreateCommand())
                        {
                            Olecmd.CommandText = string.Format(@"SELECT DISTINCT * FROM [{0}]", sheet1);
                            DataTable dtSource = new DataTable();
                            dtSource.Load(Olecmd.ExecuteReader());
                            while (dtSource.Columns.Count > 6)
                                dtSource.Columns.RemoveAt(6);
                            dtSource.Columns.Add("PeriodType", typeof(string)).DefaultValue = "month";
                            dtSource.Columns.Add("Disabled", typeof(bool)).DefaultValue = false;
                            dtSource.Columns[0].ColumnName = "ModelCode";
                            dtSource.Columns[1].ColumnName = "CateCode";
                            dtSource.Columns[2].ColumnName = "WarrantyPeriod";
                            dtSource.Columns[3].ColumnName = "Description";
                            dtSource.Columns[4].ColumnName = "ApprovedRequired";
                            dtSource.Columns[5].ColumnName = "ApprovedDuration";
                            //xóa dữ liệu trùng
                            HashSet<string> keys = new HashSet<string>();
                            bool deleted = false;
                            foreach (DataRow row in dtSource.Rows)
                            {
                                if (row["ModelCode"].Equals(DBNull.Value) || row["ModelCode"].Equals(string.Empty))
                                {
                                    row.Delete();
                                    deleted = true;
                                }
                                else
                                {
                                    if (!keys.Contains(row["ModelCode"]))
                                    {
                                        keys.Add((string)row["ModelCode"]);
                                        row["PeriodType"] = "month";
                                        row["Disabled"] = false;
                                    }
                                    else
                                    {
                                        row.Delete();
                                        deleted = true;
                                    }
                                }
                            }
                            if (deleted)
                                dtSource.AcceptChanges();
                            //Kiểm tra tồn tại nhóm sản phẩm
                            bool existsCate = pa_Category.CheckExists(dtSource.DefaultView.ToTable(true, "CateCode"));
                            if (existsCate)
                            {
                                foreach (DataColumn col in dtSource.Columns)
                                    bc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                                bc.WriteToServer(dtSource);
                            }
                            else
                            {
                                throw new CustomException("Nhóm sản phẩm chưa tồn tại. Vui lòng kiểm tra lại dữ liệu import");
                            }
                        }
                    }
                    //Xử lý dữ liệu ở bảng tạm và xóa luôn bảng tạm
                    affected = ExecuteQuery(string.Format(@"
                        UPDATE pa_Model
                        SET
                            ModelCode = T.ModelCode,
                            CateCode = T.CateCode,
                            WarrantyPeriod = T.WarrantyPeriod,
                            PeriodType = T.PeriodType,
                            [Description] = T.[Description],
                            ApprovedRequired = T.ApprovedRequired,
                            ApprovedDuration = T.ApprovedDuration,
                            [Disabled] = T.[Disabled]
                        FROM pa_Model pm INNER JOIN
                        (
	                        SELECT * FROM {0}
	                        EXCEPT 
	                        SELECT * FROM pa_Model pm
                        )T ON pm.ModelCode = T.ModelCode
                        INSERT INTO pa_Model
                        SELECT * FROM {0} T
                        WHERE T.ModelCode NOT IN (SELECT ModelCode FROM pa_Model pm)
                        DROP TABLE {0}
                        ", tempTableName));
                }
                return affected;
            }
            catch (Exception ex)
            {
                //Xóa bảng tạm
                DeleteTempTable(tempTableName);
                throw ex;
            }
        }

        public static bool CheckExists(string model)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                return context.pa_Models.Any(p => p.ModelCode == model);
            }
        }

        public static DateTime GetExpiredDate(string modelCode, DateTime registerDate)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var endDate = registerDate.AddMonths(context.pa_Models.FirstOrDefault(p => p.ModelCode == modelCode).WarrantyPeriod);
                return endDate.GetLastDayOfMonth();
            }
        }

        public static void SetApprovedRequired(bool required, string ModelCode)
        {
            ExecuteQuery("UPDATE pa_Model SET ApprovedRequired = @ApprovedRequired WHERE ModelCode = @ModelCode", new SqlParameter("@ApprovedRequired", required), new SqlParameter("@ModelCode", ModelCode));
        }
    }
}
